package com.ddllh.irontomato;

import java.io.File;

import android.database.Cursor;
import android.database.sqlite.*;

public class DbHelper {
	public static final String DATABASE_NAME_IRONTOMATO = "/sdcard/irontomato.db";
	public static final String SQLITE_MASTER = "sqlite_master";
	
	public static final String TABLE_TASK = "task";
	public static final String[] KEY_TASK = {"ID", 
		"Internal_Interrupt_Times", "External_Interrupt_Times", 
		"Descrition", "Pomodoro_Estimated", "Pomodoro_Used",
		"Is_Finished", "Is_To_Do_Today"};
	public static final int KEY_TASK_ID = 0;
	public static final int KEY_TASK_Internal_Interrupt_Times = 1;
	public static final int KEY_TASK_External_Interrupt_Times = 2;
	public static final int KEY_TASK_Is_Finished = 6;
	public static final int KEY_TASK_Is_To_Do_Today = 7;
	
	public static final String TABLE_POMODORO = "pomodoro";
	public static final String[] KEY_POMODORO = {"ID", 
		"Internal_Interrupt_Times", "External_Interrupt_Times", 
		"Date", "Start_Time", "Is_Aborted"};
	public static final int KEY_POMODORO_ID = 0;
	public static final int KEY_POMODORO_Internal_Interrupt_Times = 1;
	public static final int KEY_POMODORO_External_Interrupt_Times = 1;
	
	private static DbHelper dbHelper = new DbHelper();
	
	DbHelper(){	
	}
	
	// Get instance
	public static DbHelper getInstance(){
		return dbHelper;
	}
	
	// Open database
	private SQLiteDatabase openDB(String dbName){
		File file = new File(dbName);
		
		if (file.exists() == true){
			return (SQLiteDatabase.openDatabase(dbName, null, SQLiteDatabase.OPEN_READWRITE));
		}
		else{
			return (SQLiteDatabase.openOrCreateDatabase(dbName, null));
		}
	}
	
	// Close database
	private void closeDB(SQLiteDatabase db) {
		db.close();
	}
	
	// Delete database
	private boolean deleteDB(String dbName) {
		File file = new File(dbName);
		
		if (file.exists() == true) {
			return file.delete();	
		}
		return true;
	}
	
	// Execute SQL language
	public void execQuery(String dbName, String sql) {
		SQLiteDatabase db = openDB(dbName);
		db.execSQL(sql);
		closeDB(db);
	}
	
	// Get cursor by query 
	public Cursor openQuery(String dbName, String tableName, String conditionStr) {
		SQLiteDatabase db = openDB(dbName);
		Cursor cursor = db.query(tableName, null, conditionStr, null, null, null, null);
		cursor.moveToFirst();
		closeDB(db);
		return (cursor);
	}
	
	// Get colume name by index
	public String getColumeName(Cursor cursor, int index) {
		return(cursor.getColumnName(index));
	}
	
	// Return true when the cursor is the first one
	public boolean isBegin(Cursor cursor) {
		return(cursor.isBeforeFirst());
	}
	
	// Return true when the cursor is the last one
	public boolean isEnd(Cursor cursor) {
		return(cursor.isAfterLast());
	}
	
	// Return true when successfully move to the next one
	public boolean Next(Cursor cursor) {
		return(cursor.moveToNext());
	}
	
	// Get the content of current cursor by index
	public String getField(Cursor cursor, int index) {
		return(cursor.getString(index));
	}
	
	// Close the cursor
	public void closeCursor(Cursor cursor) {
		cursor.close();
	}
	
	// Return true when the talbe exist
	public boolean isTableExist(String dbName, String tableName) {
		Cursor cursor = openQuery(dbName, SQLITE_MASTER, "(tbl_name=" + tableName + ")");
		boolean result = cursor.getCount() > 0;
		cursor.close();
		return result;	
	}
	
	// Create table
	public void createTable() {
		if (dbHelper.isTableExist(DATABASE_NAME_IRONTOMATO, TABLE_TASK) == false){
			String sql = "CREATE TABLE" + TABLE_TASK + "(" +
					KEY_TASK[0] + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					KEY_TASK[1] + " TEXT, " +
					KEY_TASK[2] + " TEXT, " +
					KEY_TASK[3] + " TEXT, " +
					KEY_TASK[4] + " TEXT, " +
					KEY_TASK[5] + " TEXT, " +
					KEY_TASK[6] + " TEXT, " +
					KEY_TASK[7] + " TEXT)";
			execQuery(DATABASE_NAME_IRONTOMATO, sql);
		}
		if (dbHelper.isTableExist(DATABASE_NAME_IRONTOMATO, TABLE_POMODORO) == false){
			String sql = "CREATE TABLE" + TABLE_POMODORO + "(" +
					KEY_POMODORO[0] + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					KEY_POMODORO[1] + " TEXT, " +
					KEY_POMODORO[2] + " TEXT, " +
					KEY_POMODORO[3] + "TEXT, " +
					KEY_POMODORO[4] + "TEXT, " +
					KEY_POMODORO[5] + "TEXT)";
			execQuery(DATABASE_NAME_IRONTOMATO, sql);
		}
	}
	
	// Get key list for sql language 
	public String getKeyList(String[] keyList) {
		String result = keyList[0];
		for (int i = 1; i < keyList.length; i++) {
			result += ", " + keyList[i];
		}
		return result;
	}
	
	// Insert item
	public void insertItem(String tableName, Task task) {
		String sql = null;
		if  (tableName == TABLE_TASK){
			sql = "INSERT INTO " + TABLE_TASK + "" +
				"(" + getKeyList(KEY_TASK) + 
				") VALUES( " + task.getItemValue() + " )";
		}
		else if (tableName == TABLE_POMODORO){
			sql = "INSERT INTO " + TABLE_POMODORO +
				"(" + getKeyList(KEY_POMODORO) +
				") VALUES( " + task.getItemValue() + " )";
		}
		execQuery(DATABASE_NAME_IRONTOMATO, sql);
	}
	
	// Update item
	public void UpdateItem(String tableName, Task task) {
		String sql = "UPDATE " + tableName + "SET ";
		if (tableName == TABLE_TASK) {
			sql += KEY_TASK[1] + " = " + task.mColumeValue[1];
			for (int i = 2; i < task.mColumeValue.length; i++) {
				sql += ", " + KEY_TASK[i] + " = " + task.mColumeValue[i];
			}
		}
		else if (tableName == TABLE_POMODORO){
			sql += KEY_POMODORO[1] + " = " + task.mColumeValue[1];
			for (int i = 2; i < task.mColumeValue.length; i++) {
				sql += ", " + KEY_POMODORO[i] + " = " + task.mColumeValue[i];
			}
		}
		sql += " Where ID = " + task.mColumeValue[0];
		execQuery(DATABASE_NAME_IRONTOMATO, sql);
	}
	
	// Delete item
	public void DeleteItem(String tableName, Task task) {
		String sql = "DELETE FROM " + tableName +
			"WHERE ID = " + task.mColumeValue[0];
		execQuery(DATABASE_NAME_IRONTOMATO, sql);
	}
	
	// Get Cursor
	public Cursor getCursor(String tableName, String conditionStr) {
		Cursor cursor = openQuery(DATABASE_NAME_IRONTOMATO, tableName, conditionStr);
		return cursor;
	}
}